package PhoneDept;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

public class DbOper {
    private static final Log log = LogFactory.getLog(DbOper.class);
    // 创建数据库表语句
    private final static String createTable = " CREATE TABLE Phone ("
            //			+ " mobile INTEGER PRIMARY KEY NOT NULL,"
            + " mobile INTEGER NOT NULL," + " province varchar(20)," + " city varchar(30)," + " areacode varchar(10),"
            + " postcode varchar(10)," + " corp varchar(20)," + " card varchar(20));";
    // driverclass
    private final static String driverClassSqlite = "org.sqlite.JDBC";
    // url
    private final static String urlSqlite = "jdbc:sqlite:hcode.db";
    private final static String dropTable = "drop table if exists phone;";
    private final static String insertSql = " insert into Phone values (?, ?, ?, ?, ?, ?, ?);";
    private final static String xmlFilePath = "D:/mobile";
    
    public static void main(String args[]) throws Exception {
        xmlToSqliteDb();
    }
    
    public static void xmlToSqliteDb() {
        log.info("xmlToSqliteDb start");
        //读取 XML 文件，将 XML 中的数据封装到 list 中
        List<Map<String, String>> xmlInfoList = new ArrayList<Map<String, String>>(0);
        try {
            xmlInfoList = readXmlToList(xmlFilePath);
        } catch (DocumentException e) {
            log.error(e);
            e.printStackTrace();
        }
        //将读取的数据插入到数据库中
        try {
            insertDb(xmlInfoList);
        } catch (Exception e) {
            log.error(e);
            e.printStackTrace();
        }
        log.info("xmlToSqliteDb end");
    }
    
    /**
     * 操作sqlite数据库
     */
    private static void insertDb(List<Map<String, String>> dataList) throws Exception {
        log.info("insertDb start");
        Class.forName(driverClassSqlite);//加载驱动
        Connection conn = DriverManager.getConnection(urlSqlite);//获取连接
        Statement stat = conn.createStatement();//获取 statement对象
        stat.executeUpdate(dropTable);
        stat.executeUpdate(createTable);
        
        if (null != dataList && dataList.size() > 0) {
            Iterator<Map<String, String>> iterator = dataList.iterator();
            PreparedStatement prep = conn.prepareStatement(insertSql);
            
            int count = 0;//循环计数器
            while (iterator.hasNext()) {
                Map<String, String> map = iterator.next();
                
                String mobile = (String) map.get("Mobile");
                log.info("insertDb mibile:" + mobile);
                String province = (String) map.get("Province");
                String city = (String) map.get("City");
                String areacode = (String) map.get("AreaCode");
                String postcode = (String) map.get("PostCode");
                String corp = (String) map.get("Corp");
                String card = (String) map.get("Card");
                
                prep.setString(1, mobile);
                prep.setString(2, province);
                prep.setString(3, city);
                prep.setString(4, areacode);
                prep.setString(5, postcode);
                prep.setString(6, corp);
                prep.setString(7, card);
                prep.addBatch();
                
                //1000次提交一次数据
                count++;
                if (count == 10000) {
                    conn.setAutoCommit(false);
                    prep.executeBatch();
                    conn.setAutoCommit(true);
                    count = 0;
                }
            }
            conn.setAutoCommit(false);
            prep.executeBatch();
            conn.setAutoCommit(true);
            
            //关闭连接
            prep.close();
            stat.close();
            conn.close();
        }
        log.info("insertDb end");
    }
    
    /**
     * 读取 xml 文件，将文件中的信息封装到 list 中
     * @param fielPath
     * @return
     * @throws DocumentException 
     */
    private static List<Map<String, String>> readXmlToList(String _xmlFileDir) throws DocumentException {
        log.info("readXmlToList start");
        List<Map<String, String>> list = new ArrayList<Map<String, String>>(0);
        File xmlFileDir = new File(_xmlFileDir);
        if (xmlFileDir.isDirectory()) {
            File xmlFiles[] = xmlFileDir.listFiles();
            for (int i = 0; i < xmlFiles.length; i++) {
                Map<String, String> xmlInfoMap = getXmlInfo(xmlFiles[i]);
                list.add(xmlInfoMap);
            }
        }
        log.info("readXmlToList end");
        log.info("readXmlToList renturn list size:" + list.size());
        return list;
    }
    
    /**
     * 读取 XML 文件，将文件信息放到 map 中
     * @param fileName 文件路径
     * @return
     * @throws DocumentException
     */
    @SuppressWarnings("unchecked")
    private static Map<String, String> getXmlInfo(File inputXml) throws DocumentException {
        SAXReader saxReader = new SAXReader();
        Document document = saxReader.read(inputXml);
        Map<String, String> map = new HashMap<String, String>();
        
        Element rootElement = document.getRootElement();
        for (Iterator<Element> i = rootElement.elementIterator(); i.hasNext();) {
            Element element = i.next();
            map.put(element.getName(), element.getTextTrim());
        }
        return map;
    }
    
    /******************************************************************/
    /**
     * 操作accress数据库
     * @throws Exception
     */
    public void operAccess() throws Exception {
        // id,num,code,city
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String dburl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=D:/Sudii-WorkSpace/web/src/mobile.mdb";// 此为NO-DSN方式
        // String dburl ="jdbc:odbc:odbcName";//此为ODBC连接方式
        Connection conn = DriverManager.getConnection(dburl);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select Top 20 * from list ");//select Top 20 * from list
        //		System.out.println("ID\t号段\t城市编码\t城市名称");
        while (rs.next()) {
            System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t"
                    + rs.getString(4) + "\t");
        }
        rs.close();
        stmt.close();
        conn.close();
    }
    
    /**
     * 操作oracle数据库
     * @return
     */
    public Connection getOracleConnection() {
        Connection conn = null;
        try {
            // 加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // 建立数据库连接
            String url = "jdbc:oracle:thin:@172.16.1.248:1521:jtcrma";
            conn = DriverManager.getConnection(url, "jtcrm_app", "test_pass");
            
        } catch (ClassNotFoundException e) {
            System.out.println("找不到指定的驱动程序类");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public List queryOracle() throws Exception {
        List list = new ArrayList();
        Connection conn = this.getOracleConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt
                .executeQuery(" select HCODE,PROCODE,CITY_NAME,TML_CODE from pb_mobile_hcode order by hcode asc ");//,f_o_get_area_info(TML_CODE) AREA 
        System.out.println("开始时间:" + new java.text.SimpleDateFormat("yyyyMMddHHmmssMS").format(new Date()));
        while (rs.next()) {
            Map map = new HashMap();
            map.put("HCODE", rs.getString(1));
            map.put("PROCODE", rs.getString(2));
            map.put("CITY_NAME", rs.getString(3));
            map.put("TML_CODE", rs.getString(4));
            //			map.put("AREA", rs.getString(5));
            list.add(map);
        }
        rs.close();
        stmt.close();
        conn.close();
        System.out.println("结束时间:" + new java.text.SimpleDateFormat("yyyyMMddHHmmssMS").format(new Date()));
        return list;
    }
    
    private void writeResultFile(List writeFileContentList) throws IOException {
        String path = "E:/phone";
        File resultFile = new File(path);
        resultFile.createNewFile();
        //		 写文件
        FileOutputStream out = new FileOutputStream(resultFile, true);
        OutputStreamWriter output = new OutputStreamWriter(out, "GBK");
        if (null != writeFileContentList && writeFileContentList.size() > 0) {
            // 写文件头
            output.write("A");
            output.write(System.getProperty("line.separator"));
            Iterator iterator = writeFileContentList.iterator();
            while (iterator.hasNext()) {
                Map map = (Map) iterator.next();
                String cityName = (String) map.get("CITY_NAME");
                //				String area = (String)map.get("AREA");
                if (null == cityName || "".equals(cityName)) {
                    cityName = "空";
                }
                //				if ( null == area || "".equals(area)){
                //					area = "空";
                //				}
                output.write((String) map.get("HCODE"));
                output.write(";");
                output.write((String) map.get("PROCODE"));
                output.write(";");
                output.write(cityName);
                output.write(";");
                output.write((String) map.get("TML_CODE"));
                //				output.write(";");
                //				output.write(area);
                output.write(System.getProperty("line.separator"));
            }
            //			for (int i = 0; i < writeFileContentList.size(); i++) {
            //				output.write((String) writeFileContentList.get(i));
            //				output.write(System.getProperty("line.separator"));
            //			}
            // 写文件尾
            output.write("END");
            output.write(System.getProperty("line.separator"));
        }
        
        output.flush();
        output.close();
    }
}